Home:ALL Converter>Filter Array by Another Array Compare the Rows for Column A and C Matches, Keep only Non-Matches Google Apps Script [Updated]

Filter Array by Another Array Compare the Rows for Column A and C Matches, Keep only Non-Matches Google Apps Script [Updated]

Ask Time:2021-03-13T08:47:29         Author:cadmiumblue

Json Formatter

*Edit, I was able to return the opposite of what I wanted, code below. Updated with what else I've tried. I rewrote a self-contained version with the test data included so you can test/see exactly what the Logger results are yourself.

I want to create a function which compares two arrays row by row, bLFinal and matchFinal. If there is a row in bLFinal which has the same column A and column C values as a row in matchFinal, I want to delete it from bLfinal. So then all the ones with no match are the only ones left.

Here's an example of how I want the code to work. In the below example, the array to be filtered (bLFinal) is 11 rows long, and the resultant version of bLFinal should be length of 7 rows.

When any row from bLFinal matches any row in matchFinal, only comparing both arrays' A and C values (for example "penny" and "@FOX"), it removes the matched row from bLFinal.

In my actual use case, I will not necessarily know what exactly is in Col A or Col C. So I am not able to just add them to a list in the code to filter.

//bLFinal array before filtering
[
["penny", "Up", "@FOX", "45"],
["shanice", "Up", "@FOX", "45"],
["barbara", "Down", "@BEAR", "22"],
["darell", "Down", "@SHARK", "10"],
["penny", "Up", "@BUNNY", "20"],
["jules", "Up", "@FOX", "45"],
["macy", "Up", "@FOX", "45"],
["terry", "Down", "@BEAR", "22"],
["shanice", "Down", "@SHARK", "10"],
["shanice", "Up", "@BEAR", "22"],
["kelly", "Up", "@BUNNY", "20"]
]
//matchFinal array to use to check bLFinal against

[
["george", "Down", "@FOX", "45"],
["shanice", "Up", "@FOX", "45"],
["barbara", "Up", "@BEAR", "22"],
["darell", "Up", "@SHARK", "10"],
["penny", "Up", "@BUNNY", "20"],
["carol", "Down", "@LIZARD", "70"],
["bernard", "Up", "@TOAD", "85"],
["bobby", "Up", "@BUNNY", "20"]
]
//Then, the resultant version of bLFinal should look like this:

[
["penny", "Up", "@FOX", "45"],
["jules", "Up", "@FOX", "45"],
["macy", "Up", "@FOX", "45"],
["terry", "Down", "@BEAR", "22"],
["shanice", "Down", "@SHARK", "10"],
["shanice", "Up", "@BEAR", "22"],
["kelly", "Up", "@BUNNY", "20"]
]

In my above of how the arrays should work, the following lines are removed/not included in the final bLFinal, because they have the same A and C column as at least one of matchFinal's row's col A and C. (If col B or D differs, there is no problem, I only want to use for example "shanice" and "@FOX", col A and C as test criteria).

["shanice", "Up", "@FOX", "45"],
["barbara", "Down", "@BEAR", "22"],
["darell", "Down", "@SHARK", "10"],
["penny", "Up", "@BUNNY", "20"],

So namely, because rows with A col and C col values "shanice" and "@FOX", "barbara" and "@BEAR", "darell" and "@SHARK" , and finally "penny" and "@BUNNY" were found in matchFinal in its rows, they should be removed from bLFinal. I want to compare any/all rows in bLFinal to look for A and C matches in any row in matchFinal.


function bLow(){

var matchArray = [["george", "Down", "@FOX", "45"],
["shanice", "Up", "@FOX", "45"],
["barbara", "Up", "@BEAR", "22"],
["darell", "Up", "@SHARK", "10"],
["penny", "Up", "@BUNNY", "20"],
["carol", "Down", "@LIZARD", "70"],
["bernard", "Up", "@TOAD", "85"],
["bobby", "Up", "@BUNNY", "20"]];

var matchFinal = matchArray;
var matchLRow = matchArray.length;

Logger.log(matchArray.length);

var bLArray = [
["penny", "Up", "@FOX", "45"],
["shanice", "Up", "@FOX", "45"],
["barbara", "Down", "@BEAR", "22"],
["darell", "Down", "@SHARK", "10"],
["penny", "Up", "@BUNNY", "20"],
["jules", "Up", "@FOX", "45"],
["macy", "Up", "@FOX", "45"],
["terry", "Down", "@BEAR", "22"],
["shanice", "Down", "@SHARK", "10"],
["shanice", "Up", "@BEAR", "22"],
["kelly", "Up", "@BUNNY", "20"]
];

var bLFinal = bLArray;
var bLLrow = bLArray.length;

Logger.log(bLArray.length);

// This is where I'm having trouble. 
// It returns the same values for both before and after filtering of bLFinal which means it is not working.
// However, I want it to remove the 4 matching lines.

bLFinal.forEach(function(row, index){
 for (i=0 ; i<bLLrow ; i++){
   for (j=0 ; j<matchLRow ; j++){
  if (index !== 0){
    if(row[i][0] !== matchFinal[j][0] && row[i][2] !== matchFinal[j][2]){
      return;
    }
  }
   }
 } 
});
Logger.log(bLFinal.length); 
}

As an update, I was able to return the duplicates themselves using the following code

for (i=0 ; i<bLLrow ; i++){
   for (j=0 ; j<matchLRow ; j++){
     if(bLFinal[i][0] == matchFinal[j][0] && bLFinal[i][2] == matchFinal[j][2]){
      filteredBLArray.push(bLFinal[i]);
   } 
 }
 }

I'm now trying to return the opposite, the non-duplicates.

Author:cadmiumblue,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/66609018/filter-array-by-another-array-compare-the-rows-for-column-a-and-c-matches-keep
cadmiumblue :

I was able to figure it out myself. I am answering my question so that anyone who is trying to do the same or similar can use this or at least point themselves in the right direction. Hopefully there are also other answers/methods others can provide.\nAs I updated above, I was able to alter my for loop to grab the duplicates themselves (the 4 row array) using the following code which helped me change my approach:\nfor (i=0 ; i<bLLrow ; i++){\n for (j=0 ; j<matchLRow ; j++){\n if(bLFinal[i][0] == matchFinal[j][0] && bLFinal[i][2] == matchFinal[j][2]){\n filteredBLArray.push(bLFinal[i]);\n } \n }\n }\n\nThis pointed me in the right direction now look for ways to delete elements from an array.\nTo which I found the following stackoverflow question which helped me to figure out how to remove items by iterating backwards (since pop etc didn't work in the above since it messes up the array size)\nhttps://stackoverflow.com/a/28122081/12244743\nThus, the corrected for loop which grabbed all 7 of the non-duplicates is this:\nfor (i= bLFinal.length - 1 ; i>=0 ; i--){\n for (j= matchFinal.length - 1 ; j>=0 ; j--){\n if(bLFinal[i][0] == matchFinal[j][0] && bLFinal[i][2] == matchFinal[j][2]){\n bLFinal.splice(i,1);\n }\n } \n }\n \n\n(i and j can be set >= to 1 if you have header text (at row 1) in your array which you would like to exclude.\nHere is the working full code which returns the original array bLFinal less the col A and col C rows which are duplicates with the rows of matchFinal.\nfunction bLow(){\n\nvar matchArray = [["george", "Down", "@FOX", "45"],\n["shanice", "Up", "@FOX", "45"],\n["barbara", "Up", "@BEAR", "22"],\n["darell", "Up", "@SHARK", "10"],\n["penny", "Up", "@BUNNY", "20"],\n["carol", "Down", "@LIZARD", "70"],\n["bernard", "Up", "@TOAD", "85"],\n["bobby", "Up", "@BUNNY", "20"]];\n\nvar matchFinal = matchArray;\nvar matchLRow = matchArray.length;\n\nLogger.log(matchArray.length);\n\nvar bLArray = [\n["penny", "Up", "@FOX", "45"],\n["shanice", "Up", "@FOX", "45"],\n["barbara", "Down", "@BEAR", "22"],\n["darell", "Down", "@SHARK", "10"],\n["penny", "Up", "@BUNNY", "20"],\n["jules", "Up", "@FOX", "45"],\n["macy", "Up", "@FOX", "45"],\n["terry", "Down", "@BEAR", "22"],\n["shanice", "Down", "@SHARK", "10"],\n["shanice", "Up", "@BEAR", "22"],\n["kelly", "Up", "@BUNNY", "20"]\n];\n\nvar bLFinal = bLArray;\nvar bLLrow = bLArray.length;\n\nLogger.log(bLArray.length);\nvar filteredBLArray = [];\n\n\nfor (i= bLFinal.length - 1 ; i>=0 ; i--){\n for (j= matchFinal.length - 1 ; j>=0 ; j--){\n if(bLFinal[i][0] == matchFinal[j][0] && bLFinal[i][2] == matchFinal[j][2]){\n bLFinal.splice(i,1);\n }\n } \n }\n \n\nLogger.log(bLFinal);\nLogger.log(bLFinal.length);\n}\n",
2021-03-13T23:16:17
yy